package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.Tenant;
public class DBTenant implements IFDBTenant {

	private Connection  con;
	private static DBTenant dbTen;
	
	private DBTenant(){
		con = DBConnection.getInstance().getDBcon();
	}
	
	public static DBTenant getInstance(){
		if(dbTen==null) dbTen = new DBTenant();
		return dbTen;
	}
	
	
	public Tenant getTenantById(int tenantID){
		return singleWhere("t.pid='"+tenantID+"'");
	}
	
	public Tenant getTenantByName(String name){
		return singleWhere("fname LIKE '"+name+"%' OR lname LIKE '"+name+"%'");
	}
	
	public Tenant getTenantByName(String fname,String lname){
		return singleWhere("(fname LIKE '"+fname+"%' AND lname LIKE '"+lname+"%') OR (lname LIKE '"+fname+"%' AND fname LIKE '"+lname+"%')");
	}
	
	public ArrayList<Tenant> getAll(){
		return miscWhere("");
	}
	
	@Override
	public ArrayList<Tenant> getAll_where(String wClause) {
		return miscWhere(wClause);
	}

	@Override
	public ArrayList<Tenant> searchRandom(String searchF) {
		return miscWhere("t.pid LIKE '"+searchF+"' OR fname LIKE '"+searchF+"%' OR lname LIKE '"+searchF+"%' OR phone LIKE '"+searchF+"%' OR address LIKE '"+searchF+"%' OR email LIKE '"+searchF+"%' OR idNo LIKE '"+searchF+"%' OR regDate LIKE '"+searchF+"%' OR p.locationId LIKE '"+searchF+"' OR land.fname LIKE '"+searchF+"%' OR contr.startDate LIKE '"+searchF+"%'");

	}
	
	
	
	
	
	@Override
	public int getActiveTenants()
	{		
		String query = "SELECT COUNT(*) FROM Tenant WHERE pid IN (SELECT tenantId FROM Contract WHERE dateadd(mm,period,startDate)>getdate())";
		
		return (int)countQuery(query);
	}
	
	@Override
	public int getTenantsInArrear()
	{		
		String query = "SELECT COUNT(*) FROM Contract WHERE tenantId NOT IN (SELECT tenantId FROM RentalPayment WHERE month(date)=month(getdate())) AND startDate<=getdate() AND dateadd(mm,period,startDate)>=getdate()";
		
		return (int)countQuery(query);
	}
	
	
	@Override
	public double geTenantsInArrear_Money()
	{		
		String query = "SELECT SUM(monthlyRent) FROM Contract WHERE tenantId NOT IN (SELECT tenantId FROM RentalPayment WHERE month(date)=month(getdate()))  AND startDate<=getdate() AND dateadd(mm,period,startDate)>=getdate()";
		
		return countQuery(query);
	}
	
	
	private double countQuery(String query)
	{
		double count=0;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			ResultSet rs = stmt.executeQuery(query);
			if(rs.next()) count = rs.getDouble(1);
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		
		return count;
	}
	
	
	public ArrayList<Tenant> getAllTenantsByName(String name){
		return miscWhere("fname='"+name+"' OR lname='"+name+"'");
	}
	
	public int insert(Tenant tenant)
	{
		int rc = -1;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			// START Location 
			int locationId =  DBLocation.getInstance().insert(tenant.getPostalCode(), tenant.getCity(), tenant.getCountry());
			// END Location
			
			// Start People
			String query = "INSERT INTO People (fname,lname,phone,address,email,regDate,locationId) VALUES ('"+tenant.getFname()+"','"+tenant.getLname()+"','"+tenant.getPhone()+"','"+tenant.getAddress()+"','"+tenant.getEmail()+"', '"+tenant.getDate()+"', "+locationId+")";
			
			rc = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			ResultSet res = stmt.getGeneratedKeys();
			
			if (res.next())
			{
				rc = res.getInt(1);
				
				// START Tenant
				String query2 = "INSERT INTO Tenant (pid,idNo) VALUES ("+rc+",'"+tenant.getIdNo()+"')";
				rc = stmt.executeUpdate(query2);
				//END Tenant
			}
			// END People
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert tenant: " +e.getMessage());
		}
		
		return rc;
	}
	
	public int update(Tenant tenant)
	{
		int rc = -1;
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			// START Location 
			int locationId =  DBLocation.getInstance().insert(tenant.getPostalCode(), tenant.getCity(), tenant.getCountry());
			// END Location
			
			// Start People
			String query = "UPDATE People SET fname='"+tenant.getFname()+"',lname ='"+tenant.getLname()+"',phone ='"+tenant.getPhone()+"',address ='"+tenant.getAddress()+"',email ='"+tenant.getEmail()+"',regDate ='"+tenant.getDate()+"',locationId="+locationId+" WHERE pid = "+tenant.getPid()+"";
			
			PreparedStatement psstmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			rc = psstmt.executeUpdate();
			ResultSet res = psstmt.getGeneratedKeys();
			
			if (res.next())
			{
				rc = res.getInt(1);
				
				// START Tenant
				String query2 = "UPDATE Tenant SET idNo = '"+tenant.getIdNo()+"' WHERE pid = "+tenant.getPid()+"";	
				rc = stmt.executeUpdate(query2);
				//END Tenant
			}
			// END People
			stmt.close();
		}catch(Exception e){
			System.err.println("Could not update the Tenant:" + e.getMessage());
		}
		
		return rc;
	}
	
	public int delete(int tenantID){
		int rc = -1;
		String query1 = "DELETE FROM Tenant WHERE pid="+tenantID;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			stmt.close();
		}catch(Exception e){
			System.err.println("Could not delete tenant "+e.getMessage());
		}
		return rc;
	}
	
	private Tenant singleWhere(String wClause){
		Tenant tenant = new Tenant();
		
		ResultSet res;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			res = stmt.executeQuery(query);
			
			if(res.next()){
				tenant = buildTenant(res);
			}
			stmt.close();
		}catch(Exception e){
			System.err.println("Could not execute select tenant query "+e.getMessage());
		}
		
		return tenant;
	}
	
	private ArrayList<Tenant> miscWhere(String wClause)
	{
		ArrayList<Tenant> tenants = new ArrayList<>();
		
		ResultSet res;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			res = stmt.executeQuery(query);
			
			while(res.next()){
				tenants.add(buildTenant(res));
			}
			
			stmt.close();
		}catch(Exception e){
			System.err.println("Could not execute select multiple tenants query "+e.getMessage());
		}
		
		return tenants;
	}
	
	private String buildQuery(String wClause){
		String query = "SELECT t.pid,idNo,fname,lname,phone,address,email,regDate,city,postalCode,country FROM Tenant t INNER JOIN People p ON t.pid=p.pid INNER JOIN Location loc ON p.locationId=loc.locationId";
		if(wClause.length()>0) query += " WHERE "+wClause;
		
		return query;
	}
	
	private Tenant buildTenant(ResultSet res)
	{
		Tenant tenant = new Tenant();
		
		try{
			tenant.setPid(res.getInt("pid"));
			tenant.setIdNo(res.getString("idNo"));
			tenant.setFname(res.getString("fname"));
			tenant.setLname(res.getString("lname"));
			tenant.setPhone(res.getString("phone"));
			tenant.setAddress(res.getString("address"));
			tenant.setEmail(res.getString("email"));
			tenant.setDate(res.getTimestamp("regDate"));
			tenant.setPostalCode(res.getString("postalCode"));
			tenant.setCity(res.getString("city"));
			tenant.setCountry(res.getString("country"));
		}catch(Exception e) {
			System.err.println("Could not build the tenant "+e.getMessage());
		}
		return tenant;
	}


}
